{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "\n",
    "# encoding: utf-8\n",
    "import pymongo, json\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "from scipy.stats import pearsonr\n",
    "import numpy as np\n",
    "\n",
    "pd.set_option('display.width', None)  # 设置字符显示宽度\n",
    "pd.set_option('display.max_rows', None)  # 设置显示最大行\n",
    "pd.set_option('display.max_columns', None)  # 设置显示最大行\n",
    "\n",
    "client = pymongo.MongoClient('localhost', 27017)\n",
    "futures = client.futures\n",
    "position = futures.position\n",
    "market = futures.market\n",
    "unit=futures.unit\n",
    "\n",
    "start = '20190101'\n",
    "end = '20200123'\n",
    "var = 'MA'\n",
    "days=30\n",
    "\n",
    "market = DataFrame(list(market.find({'date': {'$gte': start}})))\n",
    "position = DataFrame(list(position.find({'date': {'$gte': start}})))\n",
    "unit= DataFrame(list(unit.find()))\n",
    "\n",
    "# party_name = position[position['date'] >= end]\n",
    "# party_name\n",
    "\n",
    "party_name = position[position['variety'] == var]\n",
    "long_party_name = party_name['long_party_name']\n",
    "short_party_name = party_name['short_party_name']\n",
    "party_name = long_party_name.append(short_party_name)\n",
    "party_name = party_name.drop_duplicates(keep='first').dropna()\n",
    "long = position.groupby(['date', 'variety', 'long_party_name'])[['long_openIntr']].sum()\n",
    "short = position.groupby(['date', 'variety', 'short_party_name'])[['short_openIntr']].sum()\n",
    "# 合并\n",
    "frames = [long, short]\n",
    "position = pd.concat(frames, axis=1, sort=True).fillna(0).reset_index()\n",
    "# 净持仓\n",
    "position['净持仓'] = position.apply(lambda x: x['long_openIntr'] - x['short_openIntr'], axis=1)\n",
    "# 字段更名\n",
    "position = position.rename(columns={'level_0': 'date', 'level_1': 'variety', 'level_2': 'mem'})\n",
    "vars = position[position['variety'] == var]\n",
    "\n",
    "df = pd.DataFrame()\n",
    "for i in party_name:\n",
    "    try:\n",
    "        mem = vars[vars['mem'] == i]\n",
    "        position_behind = mem.shift(-1)\n",
    "        # # 合并滞后和原始数据\n",
    "        all_position = pd.merge(position, position_behind, right_index=True, left_index=True)\n",
    "        all_position = all_position[\n",
    "            ['date_x', 'variety_x', 'mem_x', 'long_openIntr_x', 'short_openIntr_x', '净持仓_x', '净持仓_y']].dropna()\n",
    "\n",
    "        #     更名\n",
    "        all_position = all_position.rename(columns={'date_x': 'date', 'variety_x': 'variety', 'mem_x': '会员',\n",
    "                                                    'long_openIntr_x': '多单量', 'short_openIntr_x': '空单量',\n",
    "                                                    '净持仓_x': '当日净持仓', '净持仓_y': '昨日净持仓'})\n",
    "\n",
    "        all_position['净持仓变化量'] = all_position.apply(lambda x: x['当日净持仓'] - x['昨日净持仓'], axis=1)\n",
    "\n",
    "        market['change'] = market.apply(lambda x: x['close'] - x['open'], axis=1)\n",
    "        #     print(market.info())\n",
    "        vars1 = market[market['variety'] == var]\n",
    "        # print(vars1.head(5))\n",
    "        chg = vars1[['date', 'variety', 'change']]\n",
    "        chg = chg.groupby(['date', 'variety'])['change'].sum()\n",
    "\n",
    "        hb = pd.merge(chg, all_position, on=['date', 'variety'], how='outer').dropna().drop_duplicates()\n",
    "        # print(hb)\n",
    "        chgs = hb['change']\n",
    "        # print(chgs)\n",
    "        nets = hb['净持仓变化量']\n",
    "        #\n",
    "#         todayNetpostion = hb[hb['date'] <= end]\n",
    "        # print(todayNetpostion)\n",
    "        #\n",
    "        # p = pearsonr(chgs, nets)[1]\n",
    "        p=chgs.corr(nets)\n",
    "        # r = pearsonr(chgs, nets)[1]\n",
    "\n",
    "    #         p = pearsonr(todayNetpostion['change_y'],todayNetpostion['净持仓变化量'])[0]\n",
    "    #         print(p)\n",
    "\n",
    "        d=[{'var':var,'start':start,'end':end,'会员':i,'相关性':p,'样本':hb['净持仓变化量'].count(),'当日净持仓':hb['当日净持仓'].values[0],'昨日净持仓':hb['昨日净持仓'].values[0],'净持仓变化量':hb['净持仓变化量'].values[0]}]\n",
    "        df1=pd.DataFrame(d)\n",
    "        # print(df1)\n",
    "        df=df.append(df1)\n",
    "#         df== df.rolling(window=30)\n",
    "        \n",
    "\n",
    "\n",
    "    except:\n",
    "        continue\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
